// 导入数据库连接对象
const db = require("../db");
// 导入配置信息
const { debug, info } = require("../config");
const querystring = require("node:querystring");

/**
 * 获取所有文章信息
 */
exports.getArticle = async (req, res) => {
  const { page = 1, size = 5, article_title, categoryId } = req.query;
  // 计算偏移量
  // console.log(categoryId);
  const offset = (page - 1) * size;
  let sql = `select art.*,sort.name from blog_article art,blog_sort sort 
  where art.category_id=sort.id limit ${offset},${size}`;
  const sql1 = `select art.*,sort.name from blog_article art,blog_sort sort 
  where art.category_id=sort.id and art.article_title like '%${article_title}%'limit ${offset},${size}`;
  const sql2 = `select art.*,sort.name from blog_article art,blog_sort sort 
  where art.category_id=sort.id and art.category_id=${categoryId} limit ${offset},${size}`;
  if (article_title != "" && categoryId == "") {
    var [rows] = await db.promise().query(sql1);
    sql = `select count(*) as total from (${sql1}) middle_table`;
  } else if (categoryId) {
    var [rows] = await db.promise().query(sql2);
    sql = `select count(*) as total from (${sql2}) middle_table`;
  } else {
    var [rows] = await db.promise().query(sql);
    sql = `select count(*) as total from blog_article`;
  }
  // 计算总数
  const [result] = await db.promise().query(sql);
  if (rows.length != 0 && result.length != 0) {
    // 反转义文章内容
    rows.forEach((item) => {
      item.article_content = querystring.unescape(item.article_content);
      item.article_html = querystring.unescape(item.article_html);
    });
    var { total } = result[0];
    // console.log(total);
    res.send(info(0, "获取所有文章成功", { total, rows }));
  } else {
    res.send(info(0, "暂无数据", []));
  }
};
/**
 * 获取单个文章
 */
exports.getSingleArticle = async (req, res) => {
  const id = req.params.id;
  let sql = `select art.id,art.article_title,art.article_content,art.article_html,art.article_createdTime,art.article_updatedTime,art.article_views,art.article_praise,blog_sort.name,art.article_pic from blog_article art,blog_sort where art.category_id=blog_sort.id and art.id = ${id}`;
  const [rows] = await db.promise().query(sql);
  sql = `select count(com.id) as comment from blog_article art,blog_comment com where art.id=com.com_article_id and art.id = ${id}`;
  const [result] = await db.promise().query(sql);
  if (rows.length == 1) {
    // console.log(result);
    const { comment } = result[0];
    rows[0].article_content = querystring.unescape(rows[0].article_content);
    rows[0].article_html = querystring.unescape(rows[0].article_html);
    res.send(info(0, "获取指定文章成功", { ...rows[0], comment }));
  }
};
/**
 * 获取分类文章
 */
exports.getRecommend = async (req, res) => {
  const id = req.params.id;
  let sql = `select category_id from blog_article where id = ${id}`;
  const [rows] = await db.promise().query(sql);
  sql = `select * from blog_article where category_id = ${rows[0].category_id} and id!=${id} order by id desc limit 0,3`;
  const [result] = await db.promise().query(sql);
  res.send(info(0, "获取文章成功", result));
};
/**
 * 获取最热文章
 */
exports.getHotArticle = async (req, res) => {
  sql = `select id,article_title,article_updatedTime,article_pic from blog_article order by article_views desc limit 0,5`;
  const [result] = await db.promise().query(sql);
  res.send(info(0, "获取文章成功", result));
};
/**
 * 添加文章
 */
exports.addArticle = async (req, res) => {
  const { title, name, mdContent, content } = req.body;
  const con_md = querystring.escape(mdContent);
  const con_html = querystring.escape(content);
  // console.log(content, name);
  let sql = `select id from blog_sort where name="${name}"`;
  var [rows] = await db.promise().query(sql);
  if (rows.length != 1) {
    let sql1 = `insert into blog_sort (name) values("${name}")`;
    await db.promise().query(sql1);
    const [rows1] = await db.promise().query(sql);
    var { id } = rows1[0];
  } else {
    var { id } = rows[0];
  }
  sql = `insert into blog_article (article_title,category_id,article_content,article_html) values("${title}",${id},"${con_md}","${con_html}")`;
  const [result] = await db.promise().query(sql);
  if (result.affectedRows == 1) {
    // 查询最新插入数据的id
    sql = `select @@identity as id`;
    const [result1] = await db.promise().query(sql);
    if (result1) {
      res.send(info(0, "添加文章成功", result1[0]));
    }
  }
};

/**
 * 修改文章
 */
exports.updateArticle = async (req, res) => {
  const id = req.params.id;
  const { title, name, mdContent, content } = req.body;
  const con_md = querystring.escape(mdContent);
  const con_html = querystring.escape(content);
  // console.log(con);
  let sql = `select id as sort_id from blog_sort where name="${name}"`;
  var [rows] = await db.promise().query(sql);
  if (rows.length != 1) {
    let sql1 = `insert into blog_sort (name) values("${name}")`;
    await db.promise().query(sql1);
    const [rows1] = await db.promise().query(sql);
    var { sort_id } = rows1[0];
  } else {
    var { sort_id } = rows[0];
  }
  sql = `update blog_article set article_title="${title}",article_content="${con_md}",article_html="${con_html}",category_id=${sort_id} where id=${id}`;
  const [result] = await db.promise().query(sql);
  if (result.affectedRows == 1) {
    res.send(info(0, "更新文章成功", ""));
  }
};
/**
 * 添加封面
 */
exports.uploadPic = async (req, res) => {
  const id = req.params.id;
  // 图片路径
  const imageUrl = req.file
    ? `images/uploads/article_cover/${req.file.filename}`
    : "";
  let sql = `update blog_article set article_pic="${imageUrl}" where id = ${id}`;
  try {
    const [rows] = await db.promise().query(sql);
  } catch (error) {
    res.send(info(1, "数据库执行错误", debug ? error : ""));
  }
  sql = `select id,article_pic from blog_article where id=${id}`;
  const [result] = await db.promise().query(sql);
  if (result) {
    res.send(info(0, "添加文章封面成功", result[0]));
  }
};
/**
 * 更新封面
 */
exports.updatePic = async (req, res) => {
  const id = req.params.id;
  // 图片路径
  const imageUrl = req.file
    ? `images/uploads/article_cover/${req.file.filename}`
    : "";
  let sql = `update blog_article set article_pic="${imageUrl}" where id = ${id}`;
  try {
    const [rows] = await db.promise().query(sql);
    if (rows.affectedRows == 1) {
      res.send(info(0, "更新文章封面成功", ""));
    }
  } catch (error) {
    res.send(info(1, "数据库执行错误", debug ? error : ""));
  }
};
/**
 * 删除文章
 */
exports.deleteArticle = async (req, res) => {
  const { ids } = req.body;
  for (let i = 0; i < ids.length; i++) {
    let sql = `delete from blog_article where id=${ids[i]}`;
    var [rows] = await db.promise().query(sql);
  }
  res.send(info(0, "删除文章成功", ""));
};
